package canss.web.controls;

import org.apache.beehive.controls.api.bean.ControlImplementation;

import canss.supports.Makequery_select;
import canss.supports.basic;

import java.io.Serializable;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

@ControlImplementation
public class WebDBControlImpl implements WebDBControl, Serializable
{
	private static final long serialVersionUID = -4358548572989608721L;
	private static java.sql.Connection con = null;
	private static ResultSet rs = null;
	private static Statement stmt = null;

	private final static java.sql.Connection createCon()
			throws ClassNotFoundException, SQLException
	{
		if (con != null)
		{
			con.close();
			con = null;
		}
		Class.forName("com.mysql.jdbc.Driver");
		return (con = DriverManager.getConnection("jdbc:mysql://mediator.canss.net:3306/contentsmetadata_db?useUnicode=true&characterEncoding=utf-8&user=canss&password=1022"));
	}

	private final static void closeCon() throws SQLException
	{
		if (con != null)
		{
			con.close();
			con = null;
		}
	}

	/**
	 * @exception rollback
	 *                doesn't work. why?
	 * @param _query
	 * @return -1: error 0: empty
	 */

	public synchronized int[] InsertNUpdate(String _query)
	{
		if (basic.isEmpty(_query))
			return null;
		int count = 0;
		String[] _queries = _query.split(";");
		int[] retlist = new int[_queries.length];

		java.sql.Connection _con = null;

		try
		{
			_con = createCon();
			stmt = _con.createStatement();

			_con.setAutoCommit(false);

			for (String q : _queries)
			{
				if (basic.isEmpty(q))
				{
					retlist[count++] = -1;
					continue;
				}
				int ret = stmt.executeUpdate(q);
				retlist[count++] = ret;
			}
		} catch (SQLException e)
		{
			try
			{
				_con.rollback();
			} catch (SQLException e1)
			{
				e1.printStackTrace();
			}
			for (; count < _queries.length; count++)
				retlist[count] = -1;
		} catch (ClassNotFoundException e)
		{
			try
			{
				_con.rollback();
			} catch (SQLException e1)
			{
				e1.printStackTrace();
			}
			for (; count < _queries.length; count++)
				retlist[count] = -1;
		} finally
		{
			try
			{
				_con.commit();
				_con.setAutoCommit(true);
				closeCon();
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		}

		return retlist;
	}

	public synchronized LinkedList<String[]> Select(String _query)
	{

		LinkedList<String[]> result = new LinkedList<String[]>();

		if (_query == null)
			return result;

		try
		{
			stmt = createCon().createStatement();
			rs = stmt.executeQuery(_query.toString());
			if (rs != null)
			{
				// result.add(_query.getColNamesAlias()); // put col names at
				String tmpColNamesAlias = _query.substring(_query.toLowerCase().indexOf("select") + "select".length(), _query.toLowerCase().indexOf("from")).replace(" ", "");
				result.add(tmpColNamesAlias.split(","));
				// first
				int colsize = result.getFirst().length;
				while (rs.next())
				{
					String[] tmp = new String[colsize];
					for (int i = 0; i < colsize; i++)
						tmp[i] = rs.getString(i + 1);
					result.add(tmp);
				}
			}

		} catch (SQLException e)
		{
			e.printStackTrace();
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e)
			{
			}
		}
		return result;

	}

	public synchronized LinkedList<String[]> Select(Makequery_select _query)
	{
		LinkedList<String[]> result = new LinkedList<String[]>();

		if (_query == null)
			return result;

		try
		{
			stmt = createCon().createStatement();
			rs = stmt.executeQuery(_query.toString());
			if (rs != null)
			{
				result.add(_query.getColNamesAlias()); // put col names at
				// first
				int colsize = result.getFirst().length;
				while (rs.next())
				{
					String[] tmp = new String[colsize];
					for (int i = 0; i < colsize; i++)
						tmp[i] = rs.getString(i + 1);
					result.add(tmp);
				}
			}

		} catch (SQLException e)
		{
			e.printStackTrace();
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e)
			{
			}
		}
		return result;

	}

	public synchronized void RawUpdateQuery(String _query)
	{
		if (basic.isEmpty(_query))
			return;

		java.sql.Connection _con = null;
		try
		{
			_con = createCon();
			stmt = _con.createStatement();

			_con.setAutoCommit(false);

			stmt.executeUpdate(_query);
		} catch (ClassNotFoundException e1)
		{
			try
			{
				_con.rollback();
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			e1.printStackTrace();
		} catch (SQLException e1)
		{
			try
			{
				_con.rollback();
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			e1.printStackTrace();
		} finally
		{
			try
			{
				_con.commit();
				_con.setAutoCommit(true);
				closeCon();
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return;
	}
}